#!/usr/bin/env python2.5
# encoding: utf-8
"""
SQLInputStream.py

Created by Alakazam on 2007-07-05.
Copyright (c) 2007 . All rights reserved.
"""

from InputStream import InputStream
from SQLStream import SQLStream

class SQLInputStream(SQLStream, InputStream):
  streamTableStructure = """
(
  id        %s      NOT NULL PRIMARY KEY %s,
  startTime INTEGER NOT NULL,
  endTime   INTEGER NOT NULL,
  value     INTEGER NULL
)
"""
  
  def __init__(self, DBServer, name, duplicateFrom = False):
    SQLStream.__init__(self, DBServer, name)
    
    self.streamTableStructure = SQLInputStream.streamTableStructure%(
      'INTEGER'          if duplicateFrom is False else 'TEXT',
      self.DBServer.kwAI if duplicateFrom is False else '')
    
    try:
      self.DBServer.execute(
        "CREATE TABLE `" + self.name + "` " + self.streamTableStructure)
    except Exception:
      return
    
    self.DBServer.execute(
      "CREATE INDEX `" + self.name + "_startTimeIndex` ON `" + self.name + "` (startTime)")
    self.DBServer.execute(
      "CREATE INDEX `" + self.name + "_endTimeIndex` ON `" + self.name + "` (endTime)")
    self.DBServer.execute(
      "CREATE INDEX `" + self.name + "_valueIndex` ON `" + self.name + "` (value)")
    
    if not duplicateFrom is False:
      self.DBServer.execute(
        "INSERT INTO `" + self.name + "` (id, startTime, endTime, value) "
          "SELECT id, startTime, endTime, value FROM `" + duplicateFrom.name + "`");
  
  def __repr__(self):
    try:
      name = self.name
    except AttributeError:
      name = "Undefined"
    
    return "SQLInputStream('%s', '%s', structure='%s')"%(
      self.DBServer, name, self.streamTableStructure)
  
  def delete(self):
    self.DBServer.execute('DROP TABLE ' + self.name);
  
  def set(self, startTime, value, endTime = None):
    if endTime == None:
      endTime = startTime
    
    self.DBServer.execute(
      "INSERT INTO `" + self.name + "` (startTime, endTime, value) VALUES (?, ?, ?)",
      (startTime, endTime, value))
  
  def unset(self, startTime, endTime = None):
    if endTime == None:
      endTime = startTime
    
    self.DBServer.execute(
      "DELETE FROM `" + self.name + "` WHERE (startTime >= :startTime or endTime >= :startTime) AND (startTime <= :endTime OR endTime <= :endTime)",
      {"startTime": startTime, "endTime": endTime})
  
